﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;

namespace bdnt_pets_system_Bo.SystemManager
{
    public class MenuManager
    {
        //AccessHelperSQL accessHelper = AccessHelperSQL.GetInstance();
        AccessHelper accessHelper = AccessHelper.GetInstance();
        public List<Tbl_Menu> ConvertTableToObjs(DataTable dt)
        {
            List<Tbl_Menu> returnlist = new List<Tbl_Menu>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_Menu
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    MenuName = dr["MenuName"] == DBNull.Value ? "" : dr["MenuName"].ToString(),
                    MenuModule = dr["MenuModule"] == DBNull.Value ? "" : dr["MenuModule"].ToString(),
                    MenuSequence = dr["MenuName"] == DBNull.Value ? 0 : Int32.Parse(dr["MenuSequence"].ToString()),
                    PMenuId = dr["PMenuId"] == DBNull.Value ? 0 : Int32.Parse(dr["PMenuId"].ToString()),
                    Icon = dr["Icon"] == DBNull.Value ? new byte[0] : (dr["Icon"] as byte[]),
                    en_MenuName = dr["en_MenuName"] == DBNull.Value ? "" : dr["en_MenuName"].ToString(),
                    MenuType = dr["MenuType"] == DBNull.Value ? 0 : Int32.Parse(dr["MenuType"].ToString())
                });
            }
            return returnlist;
        }

        public List<Tbl_Menu> GetMainMenuList()
        {
            var sql = "select * from Tbl_Menu where PMenuId=1";
            var dt = accessHelper.ExcuteDataTable(sql);
            return ConvertTableToObjs(dt);
        }

        public List<Tbl_Menu> GetAllMenuList()
        {
            var sql = "select * from Tbl_Menu";
            var dt = accessHelper.ExcuteDataTable(sql);
            return ConvertTableToObjs(dt);
        }

        public List<Tbl_Menu> GetAllMenuListByCondition(string roleIds)
        {  
            var sql = string.Format(@"select * from Tbl_Menu where Id in (
             select distinct MenuId from Tbl_RoleMenu rm
             where rm.RoleId in ({0}) )", roleIds);

            var dt = accessHelper.ExcuteDataTable(sql);
            return ConvertTableToObjs(dt);
        }

        public bool SaveMenu(Tbl_Menu cuMenu)
        {
            try
            {
                string sql = "";
                if (cuMenu.Id == 0)
                    sql = string.Format(@"INSERT INTO Tbl_Menu
           (MenuName,MenuModule,MenuSequence,PMenuId,Icon,MenuType,en_MenuName)
            VALUES('{0}','{1}',{2},{3},:icon,{4},'{5}')", cuMenu.MenuName, cuMenu.MenuModule,
                                                                  cuMenu.MenuSequence, cuMenu.PMenuId
                                                         , cuMenu.MenuType, cuMenu.en_MenuName);
                else sql = string.Format(@"UPDATE Tbl_Menu
               SET MenuName ='{0}'
                  ,MenuModule = '{1}'
                  ,MenuSequence = {2} 
                  ,Icon = :icon 
                  ,en_MenuName='{3}'
                  ,MenuType={4}
             WHERE Id={5}", cuMenu.MenuName, cuMenu.MenuModule, cuMenu.MenuSequence, cuMenu.en_MenuName, cuMenu.MenuType, cuMenu.Id);

                //OracleCommand cmd = new OracleCommand(strUpdate, 你的连接);
                //cmd.Parameters.Add("@icon", OracleType.Blob);
                //cmd.Parameters[0].Value = buffer;
                //cmd.ExecuteNonQuery();

                //oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("image");
                //outStream = blob.getBinaryOutputStream();

                //SqlParameter p = new SqlParameter() { ParameterName = "@icon", Value = cuMenu.Icon == null ? new byte[0] : cuMenu.Icon, SqlDbType = System.Data.SqlDbType.Image };
                OracleParameter p = new OracleParameter() { ParameterName = "icon",  OracleType = OracleType.Blob};              
                if (cuMenu.Icon == null || cuMenu.Icon.Length == 0)
                    p.Value = System.DBNull.Value;
                else p.Value = cuMenu.Icon;

                //return accessHelper.ExcultData(sql, new SqlParameter[] { p });
                return accessHelper.ExcultData(sql, new OracleParameter[] { p });
            }
            catch (Exception)
            {
                return false;
                 
            }
           
        }

        public bool DeleteCuObj(Int32 objId)
        {
            string sql = string.Format(@"delete from Tbl_Menu where Id={0}", objId);
            return accessHelper.ExcultData(sql);
        }

    }
}
